-- Maximo ID
USE[NORTHWND]
GO
DECLARE @columna NVARCHAR(MAX)
        , @tabla NVARCHAR(MAX)
        , @qry NVARCHAR(MAX)
        , @max NVARCHAR(MAX)
DECLARE @temp TABLE ( Nombre VARCHAR (MAX), Columna VARCHAR(MAX), Maximo VARCHAR(MAX))
DECLARE @tc TABLE( tabla NVARCHAR (MAX), columna NVARCHAR(MAX ))
DECLARE qTablas CURSOR FOR
	SELECT QUOTENAME (t.name), c.name
		FROM sys.tables AS t
		INNER JOIN sys.columns AS c ON t.object_id = c.object_id
		WHERE c.column_id = 1
OPEN qTablas
	FETCH NEXT FROM qTablas INTO @tabla, @columna
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @qry = N'SELECT @maximo = ISNULL(MAX(' + @columna + '),0) FROM ' + @tabla
		EXEC sp_executesql @qry, N'@maximo NVARCHAR(MAX) OUTPUT', @maximo = @max OUTPUT
		INSERT INTO @temp
			VALUES(@tabla, @columna , @max )
		FETCH NEXT FROM qTablas INTO @tabla, @columna
	END
CLOSE qTablas
DEALLOCATE qTablas
SELECT * FROM @temp
GO

-- Cantidad de datos adventureworks2012
USE[AdventureWorks2012]
GO
DECLARE @tabla NVARCHAR(MAX)
		, @schema NVARCHAR(MAX)
		, @qry NVARCHAR(MAX)
		, @cant INT
DECLARE @temp TABLE(nombre VARCHAR(MAX),cantidad INT)
DECLARE qTablas CURSOR FOR
	SELECT t.name, s.name
		FROM sys.tables t
		INNER JOIN  sys.schemas s ON t.schema_id = s.schema_id
OPEN qTablas
	FETCH NEXT FROM qTablas INTO @tabla, @schema
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @qry =N'select @cantidad= count(*) from ' + @schema + '.' + @tabla
		EXEC sp_executesql @qry,N'@cantidad int OUTPUT',@cantidad=@cant OUTPUT
		INSErT INTO @temp
			VALUES(@schema + '.' +@tabla,@cant)
	
		FETCH NEXT FROM qTablas INTO @tabla, @schema
	END
CLOSE qTablas
DEALLOCATE qTablas
SELECT * FROM @temp
GO